<?php

use yii\db\Schema;
use yii\db\Migration;

class m150210_062655_add_audience_targeting_tables extends Migration
{
    public function up()
    {
		$sql =<<<FINE

CREATE TABLE {{%condition_audience}} (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  solution_id int(11) unsigned NOT NULL,
  is_whitelist int(1) NOT NULL DEFAULT 0 COMMENT '0 blacklist, 1 whitelist',
  audience_type_id text DEFAULT NULL COMMENT '人群定向 type id, 仅白名单时有值，3分号分割， 表现形式为： 人群分类;性别;年龄;收入',
  status int(1) not null default 1 comment '1 有效， 0 无效（删除或其他情况）',
  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE {{%audience_type}} (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(32) NOT NULL COMMENT '分类名称',
  pid int(11) COMMENT '父级分类 id',
  description text DEFAULT NULL COMMENT '分类说明',
  status int(1) not null default 1 comment '1 有效， 0 无效（删除或其他情况）',
  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into {{%audience_type}}(id, name, pid, description) values
(1, 'class', 0, '人群'),
(2, 'gender', 0, '性别'),
(3, 'age', 0, '年龄'),
(4, 'income', 0, '收入'),
(5, 'own crowd', 1, '自有人群'),
(6, 'male', 2, '男'),
(7, 'female', 2, '女'),
(8, '14-19', 3, '14-19岁'),
(9, '20-24', 3, '20-24岁'),
(10, '25-29', 3, '25-29岁'),
(11, '30-34', 3, '30-34岁'),
(12, '35-39', 3, '35-39岁'),
(13, '40-44', 3, '40-44岁'),
(14, '45-49', 3, '45-49岁'),
(15, 'else', 3, '其他'),
(16, '1000', 4, '<=1000'),
(17, '1001 ~ 4999', 4, '1001 ~ 4999元'),
(18, '5000 ~ 9999', 4, '5000 ~ 9999元'),
(19, '9999', 4, '>9999元');	
	
INSERT INTO {{%condition_type}}(name) values ('人群定向');

FINE;
		$this->execute($sql);
    }

    public function down()
    {
        $sql =<<<FINE

DROP TABLE IF EXISTS {{%condition_audience}};
DROP TABLE IF EXISTS {{%audience_type}};
delete from {{%condition_type}} where name = '人群定向';

FINE;
		$this->execute($sql);
    }
}
